package com.api.zszsj2.ZYT.kq.reportForms.service.impl;

import cn.hutool.core.date.DateUtil;
import com.api.zszsj2.ZYT.kq.reportForms.entity.LeaveRule;
import com.api.zszsj2.ZYT.kq.reportForms.service.KQReportFormsService;
import com.api.zszsj2.util.HrmCommon;
import com.engine.core.impl.Service;
import lombok.extern.log4j.Log4j;
import org.apache.commons.lang3.StringUtils;
import com.api.zszsj2.ZYT.util.PageBean;
import com.api.zszsj2.ZYT.util.entity.TableHeader;
import weaver.conn.RecordSet;
import weaver.general.PinyinUtil;
import weaver.hrm.User;

import java.util.*;
import java.util.stream.Collectors;

@Log4j
public class KQReportFormsServiceImpl extends Service implements KQReportFormsService {
    private static final String[] statistic_type = {"应休","已休","未休"};
    private static final String[] statistic_year = {"今年","去年"};

    @Override
    public Map tableKQReportForms(Map param, User user) {

        //获得所有下级分部ID
        String subids = HrmCommon.getAllChildSubBySubId(user.getUserSubCompany1() + "");
        subids = subids + user.getUserSubCompany1();
        //获得所有下级部门ID
        String deparids = HrmCommon.getAllChildDeptByDepId(user.getUserDepartment() + "");
        deparids = deparids + user.getUserDepartment();

        //数据源获取
        RecordSet rs = new RecordSet();
        //假期类型数据集
        List<LeaveRule> rules = new ArrayList<>();
        //数据集
        List<Map<String, Object>> data = new ArrayList<>();

        String subcompanyid = param.get("subcompanyid").toString();
        String departmentid = param.get("departmentid").toString();
        String userid = param.get("userid").toString();
        Integer currentPage = Integer.parseInt(StringUtils.isBlank(param.get("currentPage").toString()) ? "0" : param.get("currentPage").toString());
        Integer pageSize = Integer.parseInt(StringUtils.isBlank(param.get("pageSize").toString()) ? "0" : param.get("pageSize").toString());
        Integer year = Integer.parseInt(StringUtils.isBlank(param.get("year").toString()) ? DateUtil.thisYear() + "" : param.get("year").toString());

        String whereClause = String.format(" t1.departmentid in (%s) and t1.subcompanyid1 in (%s) ", deparids, subids);
        String scope = "";
        if(user.isAdmin()){
            whereClause = " 1=1 ";
        }else {
            String[] subidArr = subids.split(",");
            for (String subid : subidArr){
                scope +=subid + " = ANY (string_to_array(scopevalue, ',')) or ";
            }
            scope = " and ((" + scope.substring(0, scope.lastIndexOf("or")) + ")" + "or scopetype = 0) ";
        }
        if (StringUtils.isNotBlank(userid)) {
            whereClause += String.format(" and t1.id in(%s) ", userid);
        }
        if (StringUtils.isNotBlank(subcompanyid)) {
            whereClause += String.format(" and t1.subcompanyid1 in(%s) ", subcompanyid);
        }
        if (StringUtils.isNotBlank(departmentid)) {
            whereClause += String.format(" and t1.departmentid in(%s) ", departmentid);
        }


        //获取假期类型
        rs.execute("select id, leavename, balanceenable from KQ_LeaveRules where isenable = '1'" + scope + " order by showorder" );
        while (rs.next()) {
            LeaveRule leaveRule = new LeaveRule();
            leaveRule.setId(rs.getString("id"));
            leaveRule.setLeavename(rs.getString("leavename"));
            leaveRule.setBalanceenable(rs.getString("balanceenable"));
            rules.add(leaveRule);
        }

        //获取总记录数
        String totalCountSql = String.format("select count(1) count from hrmresource t1 where %s order by id ", whereClause);
        rs.execute(totalCountSql);
        int totalCount = 0;
        while (rs.next()) {
            totalCount = rs.getInt("count");
        }
        //构造分页
        PageBean pageBean = new PageBean(currentPage, totalCount, pageSize);

        //获取查询的用户id
        List<String> userIds = new ArrayList<>();
        String getUserIdsSql = String.format("select  " +
                        "t1.id userid, t1.lastname username, t2.id departmentid, t2.departmentname departmentname  " +
                        "from hrmresource t1  " +
                        "LEFT JOIN hrmdepartment t2 on t1.departmentid = t2.id  " +
                        "where %s order by t1.id  LIMIT %s offset %s"
                , whereClause, pageSize, pageBean.getStart());
        rs.execute(getUserIdsSql);
        while (rs.next()) {
            userIds.add(rs.getString("userid"));
            Map<String, Object> map = new HashMap<>();
            map.put("userid", rs.getString("userid"));
            map.put("username", rs.getString("username"));
            map.put("departmentid", rs.getString("departmentid"));
            map.put("departmentname", rs.getString("departmentname"));
            data.add(map);
        }


        //构造表头数据
        List<TableHeader> headers = new ArrayList<>();
        TableHeader tableHeader = new TableHeader();
        tableHeader.setTitle("所属部门");
        tableHeader.setKey("departmentname");
        tableHeader.setDataIndex(tableHeader.getKey());
        headers.add(tableHeader);
        tableHeader = new TableHeader();
        tableHeader.setTitle("所属部门id");
        tableHeader.setKey("departmentid");
        tableHeader.setDataIndex(tableHeader.getKey());
        headers.add(tableHeader);
        tableHeader = new TableHeader();
        tableHeader.setTitle("名字");
        tableHeader.setKey("username");
        tableHeader.setDataIndex(tableHeader.getKey());
        headers.add(tableHeader);
        tableHeader = new TableHeader();
        tableHeader.setTitle("用户id");
        tableHeader.setKey("userid");
        tableHeader.setDataIndex(tableHeader.getKey());
        headers.add(tableHeader);
        for (LeaveRule leaveRule : rules) {
            String name = leaveRule.getLeavename();
            if ("1".equals(leaveRule.getBalanceenable())) {
                for (String strYear : statistic_year) {
                    String key = this.getTableKey(headers, PinyinUtil.getFirstPinyins(strYear + name));
                    tableHeader = new TableHeader();
                    tableHeader.setTitle(strYear + leaveRule.getLeavename());
                    tableHeader.setKey(key);
                    tableHeader.setDataIndex(tableHeader.getKey());

                    for (String type : statistic_type) {
                        key = this.getTableKey(tableHeader.getChildren(), tableHeader.getKey() + PinyinUtil.getPinyin(type, false).get(0));
                        TableHeader children = new TableHeader();
                        children.setTitle(type);
                        children.setKey(key);
                        children.setDataIndex(children.getKey());
                        tableHeader.addChildern(children);
                    }
                    headers.add(tableHeader);
                }
            } else {
                tableHeader = new TableHeader();
                String key = this.getTableKey(headers, PinyinUtil.getFirstPinyins(name));
                tableHeader.setTitle(leaveRule.getLeavename());
                tableHeader.setKey(key);
                tableHeader.setDataIndex(tableHeader.getKey());
                headers.add(tableHeader);
            }
        }

        /**
         * leaveRule.getBalanceenable() 1   表示假期类型启用余额
         *  数据表 kq_balanceOfLeave
         * 分别统计查询出今年、去年（应休、未休、已休）数据
         */
        //获取表单数据
        String inClause = userIds.stream().map(s -> "'" + s + "'").collect(Collectors.joining(", "));
        String where = String.format(" ifnull(a.isdelete,0) <>1 and belongYear = '%s' and d.balanceenable = 1 and a.resourceId in(%s) "
                , year, inClause);
        String sql = String.format("select  " +
                "CAST(sum(ifnull(baseamount,0)+ifnull(baseamount2,0)+ifnull(tiaoxiuamount,0)+ifnull(extraamount,0)+ifnull(extraamount2,0))AS NUMERIC(10, 2)) jn_yingxiu,  " +
                "CAST(sum(ifnull(usedAmount,0)+ifnull(usedAmount2,0))AS NUMERIC(10, 2)) jn_yixiu, " +
                "CAST(sum(ifnull(baseamount,0)+ifnull(baseamount2,0)+ifnull(tiaoxiuamount,0)-ifnull(usedAmount,0)-ifnull(usedAmount2,0)+ " +
                "  ifnull(extraamount,0)+ifnull(extraamount2,0))AS NUMERIC(10, 2)) jn_weixiu, " +
                "max(d.leavename) leavename, a.resourceid from kq_balanceOfLeave a " +
                "INNER JOIN KQ_LeaveRules d on a.leaverulesid = d.id " +
                "where %s " +
                "GROUP BY a.resourceId,a.leaveRulesId", where);
        rs.execute(sql);
        while (rs.next()) {
            String resourceId = rs.getString("resourceId");
            //查出来的数据集插入Map集合
            data = data.stream().map(x -> {
                Map<String, Object> temp = new HashMap<>();
                temp.putAll(x);
                for (String key : temp.keySet()) {
                    if (key.equals("userid") && resourceId.equals(x.get(key))) {
                        String name = PinyinUtil.getFirstPinyins(rs.getString("leavename"));
                        name = this.getKey(x, name);
                        x.put("jn" + name + "yingxiu", rs.getString("jn_yingxiu"));
                        x.put("jn" + name + "yixiu", rs.getString("jn_yixiu"));
                        x.put("jn" + name + "weixiu", rs.getString("jn_weixiu"));
                        break;
                    }
                }
                return x;
            }).collect(Collectors.toList());
        }
        //去年数据
        where = String.format(" ifnull(a.isdelete,0) <>1 and belongYear = '%s'  and d.balanceenable = 1 and a.resourceId in(%s) "
                , (year - 1), inClause);
        sql = String.format("select  " +
                "CAST(sum(ifnull(baseamount,0)+ifnull(baseamount2,0)+ifnull(tiaoxiuamount,0)+ifnull(extraamount,0)+ifnull(extraamount2,0)) AS NUMERIC(10, 2))qn_yingxiu,  " +
                "CAST(sum(ifnull(usedAmount,0)+ifnull(usedAmount2,0))AS NUMERIC(10, 2)) qn_yixiu, " +
                "CAST(sum(ifnull(baseamount,0)+ifnull(baseamount2,0)+ifnull(tiaoxiuamount,0)-ifnull(usedAmount,0)-ifnull(usedAmount2,0)+ " +
                "  ifnull(extraamount,0)+ifnull(extraamount2,0))AS NUMERIC(10, 2)) qn_weixiu, " +
                "max(d.leavename) leavename, a.resourceid from kq_balanceOfLeave a " +
                "INNER JOIN KQ_LeaveRules d on a.leaverulesid = d.id " +
                "where %s " +
                "GROUP BY a.resourceId,a.leaveRulesId", where);
        rs.execute(sql);
        while (rs.next()) {
            String resourceId = rs.getString("resourceId");
            //查出来的数据集插入Map集合
            data = data.stream().map(x -> {
                Map<String, Object> temp = new HashMap<>();
                temp.putAll(x);
                for (String key : temp.keySet()) {
                    if (key.equals("userid") && resourceId.equals(x.get(key))) {
                        String name = PinyinUtil.getFirstPinyins(rs.getString("leavename"));
                        name = this.getKey(x, name);
                        x.put("qn" + name + "yingxiu", rs.getString("qn_yingxiu"));
                        x.put("qn" + name + "yixiu", rs.getString("qn_yixiu"));
                        x.put("qn" + name + "weixiu", rs.getString("qn_weixiu"));
                        break;
                    }
                }
                return x;
            }).collect(Collectors.toList());
        }

        where = String.format(" EXTRACT(YEAR FROM fromdate::date) = '%s' and a.resourceId in(%s) "
                , year, inClause);
//        sql = String.format("select t1.*, t0.leavename from KQ_LeaveRules t0 " +
//                "right  join( " +
//                "select  " +
//                "sum(duration) duration, resourceId, newleavetype " +
//                "from  kq_flow_split_leave a " +
//                "where %s " +
//                "GROUP BY  resourceId, newleavetype " +
//                ") t1 on t0.id = t1.newleavetype " +
//                "where t0.balanceenable = 0 " +
//                "ORDER BY resourceid", where);
        sql = String.format("SELECT " +
                "   t1.leavename, t1.resourceid, t1.newleavetype, t1.duration - ifnull ( t2.duration, 0 ) duration  " +
                "FROM ( " +
                "   SELECT " +
                "      t1.*, t0.leavename  " +
                "   FROM " +
                "      KQ_LeaveRules t0 RIGHT JOIN ( " +
                "      SELECT SUM " +
                "         (A.duration) duration, A.resourceId, A.newleavetype  " +
                "      FROM " +
                "         kq_flow_split_leave A  " +
                "      where %s " +
                "      GROUP BY " +
                "         A.resourceId, A.newleavetype  " +
                "      ) t1 ON t0.ID = t1.newleavetype  " +
                "   WHERE " +
                "      t0.balanceenable = 0  " +
                "   ) t1 " +
                "   LEFT JOIN ( SELECT newleavetype, resourceid, SUM (duration) duration FROM kq_flow_split_leaveback GROUP BY newleavetype, resourceid ) t2 ON t1.resourceId = t2.resourceId  " +
                "   AND t1.newleavetype = t2.newleavetype  " +
                "ORDER BY " +
                "   t1.resourceid", where);
        rs.execute(sql);
        while (rs.next()) {
            String resourceId = rs.getString("resourceId");
            data = data.stream().map(x -> {
                Map<String, Object> temp = new HashMap<>();
                temp.putAll(x);
                for (String key : temp.keySet()) {
                    if (key.equals("userid") && resourceId.equals(x.get(key))) {
                        String name = PinyinUtil.getFirstPinyins(rs.getString("leavename"));
                        name = this.getKey(x, name);
                        x.put(name, rs.getString("duration"));
                        break;
                    }
                }
                return x;
            }).collect(Collectors.toList());
        }
        pageBean.setList(data);
        Map res = new HashMap();
        res.put("headers", headers);
        res.put("pageBean", pageBean);
        return res;
    }

    @Override
    public Map tableKQReportFormsLeaveDetail(Map param, User user) {
        //获得所有下级分部ID
        String subids = HrmCommon.getAllChildSubBySubId(user.getUserSubCompany1() + "");
        subids = subids + user.getUserSubCompany1();
        //获得所有下级部门ID
        String deparids = HrmCommon.getAllChildDeptByDepId(user.getUserDepartment() + "");
        deparids = deparids + user.getUserDepartment();


        RecordSet rs = new RecordSet();
        List<Map<String, Object>> data = new ArrayList<>();

        String subcompanyid = param.get("subcompanyid").toString();
        String departmentid = param.get("departmentid").toString();
        String userid = param.get("userid").toString();
        String begindate = param.get("begindate").toString();
        String enddate = param.get("enddate").toString();
        String year = param.get("year").toString();
        Integer currentPage = Integer.parseInt(StringUtils.isBlank(param.get("currentPage").toString()) ? "0" : param.get("currentPage").toString());
        Integer pageSize = Integer.parseInt(StringUtils.isBlank(param.get("pageSize").toString()) ? "0" : param.get("pageSize").toString());

        String whereClause = "a.id in(select max(id) from kq_flow_split_leave GROUP BY requestid) ";
        if(!user.isAdmin()){
            whereClause += String.format(" and d.departmentid in (%s) and d.subcompanyid1 in (%s) ", deparids, subids);
        }
        if (StringUtils.isNotBlank(userid)) {
            whereClause += String.format(" and d.id in(%s) ", userid);
        }
        if (StringUtils.isNotBlank(subcompanyid)) {
            whereClause += String.format(" and d.subcompanyid1 in(%s) ", subcompanyid);
        }
        if (StringUtils.isNotBlank(departmentid)) {
            whereClause += String.format(" and d.departmentid in(%s) ", departmentid);
        }
        if (StringUtils.isNotBlank(year)) {
            whereClause += String.format(" and (EXTRACT(YEAR FROM a.fromdatedb::date) = %s or EXTRACT(YEAR FROM a.todatedb::date) = %s ) ", year, year);
        }
        if (StringUtils.isNotBlank(begindate) || StringUtils.isNotBlank(enddate)) {
            whereClause += String.format(" and (a.fromdatedb BETWEEN '%s' AND '%s' " +
                    "        OR a.todatedb BETWEEN '%s' AND '%s' " +
                    "        OR '%s' BETWEEN a.fromdatedb AND a.todatedb " +
                    "        OR '%s' BETWEEN a.fromdatedb AND a.todatedb) ", begindate, begindate, begindate, begindate, begindate, begindate);
        }
        String sql =  String.format("select  " +
                        "count(1) count " +
                        "from kq_flow_split_leave a  " +
                        "INNER JOIN workflow_requestbase b on b.requestid = a.requestid " +
                        "INNER JOIN KQ_LeaveRules c on c.id = a.newleavetype " +
                        "INNER JOIN hrmresource d on d.id = b.creater " +
                        "INNER JOIN hrmdepartment e on e.id = d.departmentid " +
                        "INNER JOIN workflow_nodebase f on f.id = b.currentnodeid " +
                        "LEFT JOIN  (SELECT requestid, max(durationdb) durationdb FROM kq_flow_split_leaveback GROUP BY requestid) g on instr(a.leavebackrequestid, g.requestid) > 0 " +
                        " where %s ", whereClause);
        rs.execute(sql);
        int totalCount = 0;
        while (rs.next()) {
            totalCount = rs.getInt("count");
        }
        PageBean pageBean = new PageBean(currentPage, totalCount, pageSize);

        sql =  String.format("select  " +
                "  e.departmentname 部门名称, d.lastname 申请人, c.leavename 请假类型, a.fromdatedb 休假开始时间 " +
                ", a.todatedb 休假结束时间, f.nodename 当前环节, b.createdate 申请时间 " +
                ", CASE " +
                "    WHEN c.minimumunit in (3,5,6)    THEN concat(a.durationdb, '(小时)') " +
                "    ELSE  concat(a.durationdb, '(天)') " +
                "END 休假天数  " +
                ", CASE " +
                "    WHEN b.currentnodetype = 0    THEN '未提交' " +
                "    WHEN b.currentnodetype = 3    THEN '审批通过' " +
                "    ELSE '审批中' " +
                "END 审批状态  " +
                ", CASE " +
                "    WHEN a.durationdb - ifnull(g.durationdb, 0) = a.durationdb    THEN '未销假' " +
                "    WHEN a.durationdb - ifnull(g.durationdb, 0) = 0    THEN '已销假' " +
                "    ELSE '部分销假' " +
                "END 销假状态  " +
                "from kq_flow_split_leave a  " +
                "INNER JOIN workflow_requestbase b on b.requestid = a.requestid " +
                "INNER JOIN KQ_LeaveRules c on c.id = a.newleavetype " +
                "INNER JOIN hrmresource d on d.id = a.resourceid " +
                "INNER JOIN hrmdepartment e on e.id = d.departmentid " +
                "INNER JOIN workflow_nodebase f on f.id = b.currentnodeid " +
                "LEFT JOIN  (SELECT requestid, max(durationdb) durationdb FROM kq_flow_split_leaveback GROUP BY requestid) g on instr(a.leavebackrequestid, g.requestid) > 0 " +
                " where %s " +
                " ORDER BY e.id, d.id " +
                "LIMIT %s offset %s "
        , whereClause, pageSize, pageBean.getStart() < 0 ? 0 : pageBean.getStart());
        rs.execute(sql);
        //获取所有的列   columnNames 只做查询，不能新增或者删除
        List<String> columnNames =  Arrays.asList( rs.getColumnName());
        //构造表头
        List<TableHeader> headers = new ArrayList<>();
        columnNames.forEach(columnName -> {
            TableHeader tableHeader = new TableHeader();
            String key = this.getTableKey(headers, PinyinUtil.getFirstPinyins(columnName));
            tableHeader.setKey(key);
            tableHeader.setTitle(columnName);
            tableHeader.setDataIndex(key);
            headers.add(tableHeader);
        });
        //获取数据
        while (rs.next()) {
            Map map = new HashMap();
            columnNames.forEach(columnName -> {
                map.put(this.getKey(map, PinyinUtil.getFirstPinyins(columnName)), rs.getString(columnName));

            });
            data.add(map);
        }
        pageBean.setList(data);
        Map res = new HashMap();
        res.put("headers", headers);
        res.put("pageBean", pageBean);
        return res;
    }

    @Override
    public Map tableKQReportFormsBackDetail(Map param, User user) {

        //获得所有下级分部ID
        String subids = HrmCommon.getAllChildSubBySubId(user.getUserSubCompany1() + "");
        subids = subids + user.getUserSubCompany1();
        //获得所有下级部门ID
        String deparids = HrmCommon.getAllChildDeptByDepId(user.getUserDepartment() + "");
        deparids = deparids + user.getUserDepartment();


        RecordSet rs = new RecordSet();
        List<Map<String, Object>> data = new ArrayList<>();

        String subcompanyid = param.get("subcompanyid").toString();
        String departmentid = param.get("departmentid").toString();
        String userid = param.get("userid").toString();
        String begindate = param.get("begindate").toString();
        String enddate = param.get("enddate").toString();
        String year = param.get("year").toString();
        Integer currentPage = Integer.parseInt(StringUtils.isBlank(param.get("currentPage").toString()) ? "0" : param.get("currentPage").toString());
        Integer pageSize = Integer.parseInt(StringUtils.isBlank(param.get("pageSize").toString()) ? "0" : param.get("pageSize").toString());

        String whereClause = "a.id in(select max(id) from kq_flow_split_leaveback GROUP BY requestid) ";
        if(!user.isAdmin()){
            whereClause += String.format(" and d.departmentid in (%s) and d.subcompanyid1 in (%s) ", deparids, subids);
        }

        if (StringUtils.isNotBlank(userid)) {
            whereClause += String.format(" and d.id in(%s) ", userid);
        }
        if (StringUtils.isNotBlank(subcompanyid)) {
            whereClause += String.format(" and d.subcompanyid1 in(%s) ", subcompanyid);
        }
        if (StringUtils.isNotBlank(departmentid)) {
            whereClause += String.format(" and d.departmentid in(%s) ", departmentid);
        }
        if (StringUtils.isNotBlank(year)) {
            whereClause += String.format(" and (EXTRACT(YEAR FROM a.fromdatedb::date) = %s or EXTRACT(YEAR FROM a.todatedb::date) = %s ) ", year, year);
        }
        if (StringUtils.isNotBlank(begindate) || StringUtils.isNotBlank(enddate)) {
            whereClause += String.format(" and (a.fromdatedb BETWEEN '%s' AND '%s' " +
                    "        OR a.todatedb BETWEEN '%s' AND '%s' " +
                    "        OR '%s' BETWEEN a.fromdatedb AND a.todatedb " +
                    "        OR '%s' BETWEEN a.fromdatedb AND a.todatedb) ", begindate, begindate, begindate, begindate, begindate, begindate);
        }
        String sql =  String.format("select   " +
                "count(1) count " +
                "from kq_flow_split_leaveback a " +
                "INNER JOIN workflow_requestbase b on b.requestid = a.requestid " +
                "INNER JOIN KQ_LeaveRules c on c.id = a.newleavetype " +
                "INNER JOIN hrmresource d on d.id = a.resourceid " +
                "INNER JOIN hrmdepartment e on e.id = d.departmentid " +
                " where %s ", whereClause);
        rs.execute(sql);
        int totalCount = 0;
        while (rs.next()) {
            totalCount = rs.getInt("count");
        }
        PageBean pageBean = new PageBean(currentPage, totalCount, pageSize);

        sql =  String.format("select   " +
                        "e.departmentname 部门名称 , d.lastname 申请人, c.leavename 请假类型, a.fromdatedb 请假类型 " +
                        ", a.todatedb 休假结束时间, b.createdate 申请时间 " +
                        ", CASE " +
                        "    WHEN c.minimumunit in (3,5,6)    THEN concat(a.durationdb, '(小时)') " +
                        "    ELSE  concat(a.durationdb, '(天)') " +
                        "END 休假天数  " +
                        ", CASE " +
                        "    WHEN b.currentnodetype = 0    THEN '未提交' " +
                        "    WHEN b.currentnodetype = 3    THEN '审批通过' " +
                        "    ELSE '审批中' " +
                        "END 审批状态 " +
                        "from kq_flow_split_leaveback a " +
                        "INNER JOIN workflow_requestbase b on b.requestid = a.requestid " +
                        "INNER JOIN KQ_LeaveRules c on c.id = a.newleavetype " +
                        "INNER JOIN hrmresource d on d.id = a.resourceid " +
                        "INNER JOIN hrmdepartment e on e.id = d.departmentid " +
                        "where %s " +
                        "ORDER BY e.id, d.id " +
                        "LIMIT %s offset %s "
                , whereClause, pageSize, pageBean.getStart() < 0 ? 0 : pageBean.getStart());
        rs.execute(sql);
        //获取所有的列   columnNames 只做查询，不能新增或者删除
        List<String> columnNames =  Arrays.asList( rs.getColumnName());
        //构造表头
        List<TableHeader> headers = new ArrayList<>();
        columnNames.forEach(columnName -> {
            TableHeader tableHeader = new TableHeader();
            String key = this.getTableKey(headers, PinyinUtil.getFirstPinyins(columnName));
            tableHeader.setKey(key);
            tableHeader.setTitle(columnName);
            tableHeader.setDataIndex(key);
            headers.add(tableHeader);
        });
        //获取数据
        while (rs.next()) {
            Map map = new HashMap();
            columnNames.forEach(columnName -> {
                map.put(this.getKey(map, PinyinUtil.getFirstPinyins(columnName)), rs.getString(columnName));
            });
            data.add(map);
        }
        pageBean.setList(data);
        Map res = new HashMap();
        res.put("headers", headers);
        res.put("pageBean", pageBean);
        return res;
    }

    @Override
    public Map tableKQReportFormsOverTime(Map param, User user) {

        //获得所有下级分部ID
        String subids = HrmCommon.getAllChildSubBySubId(user.getUserSubCompany1() + "");
        subids = subids + user.getUserSubCompany1();
        //获得所有下级部门ID
        String deparids = HrmCommon.getAllChildDeptByDepId(user.getUserDepartment() + "");
        deparids = deparids + user.getUserDepartment();

        //数据源获取
        RecordSet rs = new RecordSet();
        //数据集
        List<Map<String, Object>> data = new ArrayList<>();

        String subcompanyid = param.get("subcompanyid").toString();
        String departmentid = param.get("departmentid").toString();
        String userid = param.get("userid").toString();
        Integer currentPage = Integer.parseInt(StringUtils.isBlank(param.get("currentPage").toString()) ? "0" : param.get("currentPage").toString());
        Integer pageSize = Integer.parseInt(StringUtils.isBlank(param.get("pageSize").toString()) ? "0" : param.get("pageSize").toString());
        Integer year = Integer.parseInt(StringUtils.isBlank(param.get("year").toString()) ? DateUtil.thisYear() + "" : param.get("year").toString());

        String whereClause = "1=1";
        if(!user.isAdmin()){
            whereClause += String.format(" and departmentid in (%s) and subcompanyid1 in (%s) ", deparids, subids);
        }
        if (StringUtils.isNotBlank(userid)) {
            whereClause += String.format(" and id in(%s) ", userid);
        }
        if (StringUtils.isNotBlank(subcompanyid)) {
            whereClause += String.format(" and subcompanyid1 in(%s) ", subcompanyid);
        }
        if (StringUtils.isNotBlank(departmentid)) {
            whereClause += String.format(" and departmentid in(%s) ", departmentid);
        }

        //获取总记录数
        String totalCountSql = String.format("select count(1) count from hrmresource where %s order by id ", whereClause);
        rs.execute(totalCountSql);
        int totalCount = 0;
        while (rs.next()) {
            totalCount = rs.getInt("count");
        }
        //构造分页
        PageBean pageBean = new PageBean(currentPage, totalCount, pageSize);

        //获取查询的用户id
        List<String> userIds = new ArrayList<>();
        String getUserIdsSql = String.format("select id from hrmresource where %s order by id  LIMIT %s offset %s"
                , whereClause, pageSize, pageBean.getStart());
        rs.execute(getUserIdsSql);
        while (rs.next()) {
            userIds.add(rs.getString("id"));
        }
        String inClause = userIds.stream().map(s -> "'" + s + "'").collect(Collectors.joining(", "));
        String sql = String.format(" " +
                "select  " +
                "a.lastname, d.departmentname " +
                ", ifnull(b.jnyx,0)今年应休, ifnull(b.jnyx_1,0)今年已休 " +
                ", ifnull(b.jnwx,0)今年未休, ifnull(c.qnyx,0)去年应休 " +
                ", ifnull(c.qnyx_1,0)去年已休, ifnull(c.qnwx,0)去年未休 " +
                "from hrmresource a  " +
                "left JOIN(  " +
                "select resourceId " +
                ", sum(ifnull(baseamount,0)+ifnull(baseamount2,0)+ifnull(tiaoxiuamount,0)+ifnull(extraamount,0)+ifnull(extraamount2,0)) jnyx " +
                ", sum(ifnull(usedAmount,0)+ifnull(usedAmount2,0)) jnyx_1 " +
                ", sum(ifnull(baseamount,0)+ifnull(baseamount2,0)+ifnull(tiaoxiuamount,0)-ifnull(usedAmount,0)-ifnull(usedAmount2,0)+ " +
                "  ifnull(extraamount,0)+ifnull(extraamount2,0)) jnwx " +
                "from kq_balanceOfLeave where resourceId in("+inClause+") and belongYear= " + year +" and  leaveRulesId= 6 and ifnull(isdelete,0) <>1 " +
                "GROUP BY resourceId  " +
                ")b on b.resourceId = a.id " +
                "left JOIN(  " +
                "select resourceId " +
                ", sum(ifnull(baseamount,0)+ifnull(baseamount2,0)+ifnull(tiaoxiuamount,0)+ifnull(extraamount,0)+ifnull(extraamount2,0)) qnyx " +
                ", sum(ifnull(usedAmount,0)+ifnull(usedAmount2,0)) qnyx_1 " +
                ", sum(ifnull(baseamount,0)+ifnull(baseamount2,0)+ifnull(tiaoxiuamount,0)-ifnull(usedAmount,0)-ifnull(usedAmount2,0)+ " +
                "  ifnull(extraamount,0)+ifnull(extraamount2,0)) qnwx " +
                " from kq_balanceOfLeave where resourceId in("+inClause+") and belongYear= " + (year-1) +" and  leaveRulesId= 6 and ifnull(isdelete,0) <>1 " +
                "GROUP BY resourceId  " +
                ")c on c.resourceId = a.id " +
                "INNER JOIN hrmdepartment d on d.id = a.departmentid " +
                "where  a.id in(%s)" +
                "ORDER BY b.resourceId, c.resourceId ", inClause);
        rs.execute(sql);
        //获取所有的列   columnNames 只做查询，不能新增或者删除
        List<String> columnNames =  Arrays.asList( rs.getColumnName());
        //构造表头
        List<TableHeader> headers = new ArrayList<>();
        columnNames.forEach(columnName -> {
            TableHeader tableHeader = new TableHeader();
            String key = this.getTableKey(headers, PinyinUtil.getFirstPinyins(columnName));
            tableHeader.setKey(key);
            tableHeader.setTitle(columnName);
            tableHeader.setDataIndex(key);
            headers.add(tableHeader);
        });
        //获取数据
        while (rs.next()) {
            Map map = new HashMap();
            columnNames.forEach(columnName -> {
                map.put(this.getKey(map, PinyinUtil.getFirstPinyins(columnName)), rs.getString(columnName));

            });
            data.add(map);
        }
        pageBean.setList(data);
        Map res = new HashMap();
        res.put("headers", headers);
        res.put("pageBean", pageBean);
        return res;
    }

    @Override
    public Map tableKQReportFormsOverTimeDetail(Map param, User user) {
        //获得所有下级分部ID
        String subids = HrmCommon.getAllChildSubBySubId(user.getUserSubCompany1() + "");
        subids = subids + user.getUserSubCompany1();
        //获得所有下级部门ID
        String deparids = HrmCommon.getAllChildDeptByDepId(user.getUserDepartment() + "");
        deparids = deparids + user.getUserDepartment();

        RecordSet rs = new RecordSet();
        List<Map<String, Object>> data = new ArrayList<>();

        String subcompanyid = param.get("subcompanyid").toString();
        String departmentid = param.get("departmentid").toString();
        String userid = param.get("userid").toString();
        String begindate = param.get("begindate").toString();
        String enddate = param.get("enddate").toString();
        String year = param.get("year").toString();
        Integer currentPage = Integer.parseInt(StringUtils.isBlank(param.get("currentPage").toString()) ? "0" : param.get("currentPage").toString());
        Integer pageSize = Integer.parseInt(StringUtils.isBlank(param.get("pageSize").toString()) ? "0" : param.get("pageSize").toString());

        String whereClause = "a.id in(select max(id) from kq_flow_split_overtime GROUP BY requestid) ";
        if(!user.isAdmin()){
            whereClause += String.format(" and d.departmentid in (%s) and d.subcompanyid1 in (%s) ", deparids, subids);
        }
        if (StringUtils.isNotBlank(userid)) {
            whereClause += String.format(" and d.id in(%s) ", userid);
        }
        if (StringUtils.isNotBlank(subcompanyid)) {
            whereClause += String.format(" and d.subcompanyid1 in(%s) ", subcompanyid);
        }
        if (StringUtils.isNotBlank(departmentid)) {
            whereClause += String.format(" and d.departmentid in(%s) ", departmentid);
        }
        if (StringUtils.isNotBlank(year)) {
            whereClause += String.format(" and (EXTRACT(YEAR FROM a.fromdatedb::date) = %s or EXTRACT(YEAR FROM a.todatedb::date) = %s ) ", year, year);
        }
        if (StringUtils.isNotBlank(begindate) || StringUtils.isNotBlank(enddate)) {
            whereClause += String.format(" and (a.fromdatedb BETWEEN '%s' AND '%s' " +
                    "        OR a.todatedb BETWEEN '%s' AND '%s' " +
                    "        OR '%s' BETWEEN a.fromdatedb AND a.todatedb " +
                    "        OR '%s' BETWEEN a.fromdatedb AND a.todatedb) ", begindate, begindate, begindate, begindate, begindate, begindate);
        }
        String sql =  String.format("select   " +
                "count(1) count " +
                "from kq_flow_split_overtime a " +
                "INNER JOIN hrmresource d on d.id = a.resourceid " +
                "INNER JOIN hrmdepartment e on e.id = d.departmentid " +
                " where %s ", whereClause);
        rs.execute(sql);
        int totalCount = 0;
        while (rs.next()) {
            totalCount = rs.getInt("count");
        }
        PageBean pageBean = new PageBean(currentPage, totalCount, pageSize);
        sql =  String.format(" " +
                        "select  " +
                        "e.departmentname 部门名称, d.lastname 名字 , a.fromdatedb 开始时间, todatedb 结束时间, a.durationdb 加班时长 " +
                        "from kq_flow_split_overtime a " +
                        "INNER JOIN hrmresource d on d.id = a.resourceid " +
                        "INNER JOIN hrmdepartment e on e.id = d.departmentid " +
                        "where %s  " +
                        "ORDER BY e.id, d.id " +
                        "LIMIT %s offset %s "
                , whereClause, pageSize, pageBean.getStart() < 0 ? 0 : pageBean.getStart());
        rs.execute(sql);
        //获取所有的列   columnNames 只做查询，不能新增或者删除
        List<String> columnNames =  Arrays.asList( rs.getColumnName());
        //构造表头
        List<TableHeader> headers = new ArrayList<>();
        columnNames.forEach(columnName -> {
            TableHeader tableHeader = new TableHeader();
            String key = this.getTableKey(headers, PinyinUtil.getFirstPinyins(columnName));
            tableHeader.setKey(key);
            tableHeader.setTitle(columnName);
            tableHeader.setDataIndex(key);
            headers.add(tableHeader);
        });
        //获取数据
        while (rs.next()) {
            Map map = new HashMap();
            columnNames.forEach(columnName -> {
                map.put(this.getKey(map, PinyinUtil.getFirstPinyins(columnName)), rs.getString(columnName));
            });
            data.add(map);
        }
        pageBean.setList(data);
        Map res = new HashMap();
        res.put("headers", headers);
        res.put("pageBean", pageBean);
        return res;
    }


    public  static String getTableKey(List<TableHeader> list, String name) {
        if (list == null){
            list = new ArrayList<>();
        }
        for (TableHeader header : list) {
            if (name.equals(header.getKey())) {
                String[] sp = name.split("_");
                if (sp.length > 1) {
                    int index = Integer.parseInt(sp[1]) + 1;
                    name = sp[0] + "_" + index;
                } else {
                    name += "_1";
                }
                name = getTableKey(list, name);
            }
        }
        return name;
    }

    public  static String getKey(Map<String, Object> map, String name){
        for (Map.Entry<String, Object> entry : map.entrySet()){
            if (name.equals(entry.getKey())){
                String[] sp = name.split("_");
                if(sp.length > 1){
                    int index = Integer.parseInt(sp[1]) + 1;
                    name = sp[0] + "_" + index;
                }else{
                    name += "_1";
                }
                name = getKey(map, name);
            }
        }
        return  name;
    }


    public static void main(String[] args) {

        String py = "育儿假";
        System.out.println(PinyinUtil.getFirstPinyins("sasdasqvv"));



//        TableHeader(title=今年护理假-应休, key=jnyx, dataIndex=jnyx)
    }

}
